<?php

class WoodleDBManager {

	var $course_table;
	var $lecture_table;
	var $material_table;
	
	var $posts_table;

	function __construct() {
		global $wpdb;
		$this -> course_table = $wpdb -> prefix . "woo_course";
		$this -> lecture_table = $wpdb -> prefix . "woo_lecture";
		$this -> material_table = $wpdb -> prefix . "woo_material";
		
		$this -> posts_table = $wpdb -> prefix ."posts";
	}
	
	function createCourse($name, $description, $semester, $year){
		global $wpdb;
		$wpdb -> insert($this -> course_table, array('semester' => $semester, 'name' => $name, 'description' => $description, 'year' => $year),
														array( '%s', '%s' , '%s', '%d' ));
		return $wpdb->insert_id;
	}
	
	function createLecture($course_id, $name, $description, $date){
		global $wpdb;
		$err = $wpdb -> insert($this -> lecture_table, array('course_id' => $course_id, 'name' => $name, 'description' => $description, 'date' => $date),
														array( '%d', '%s' , '%s', '%s' ));
		return $wpdb->insert_id;
	}
	
	function createMaterial($lecture_id, $name, $url, $type){
		global $wpdb;
		$err = $wpdb -> insert($this->material_table, array('lecture_id' => $lecture_id, 'name' => $name, 'url_id' => $url, 'type' => $type),
														array( '%d', '%s' , '%d', '%s' ));
		return $wpdb->insert_id;
	}
	
	function updateLecture($lecture_id, $name, $description, $date){
		global $wpdb;
		$err =  $wpdb -> update($this -> lecture_table, array('name' => $name, 'description' => $description, 'date' => $date),
														array('lecture_id' => $lecture_id),
														array( '%s' , '%s', '%s' ));
		return $err;
	}
	
	function updateCourse($id, $name, $description, $semester, $year){
		if ($name == "" || $description == "") {
			return false;
		}
		global $wpdb;
		return $wpdb -> update($this -> course_table, array(
														'semester' => $semester, 
														'name' => $name, 
														'description' => $description, 
														'year' => $year),
													  array('course_id' => $id),
													  array( 
													  	'%s', 
													  	'%s', 
													  	'%s', 
													  	'%d' ));
	}
	
	function duplicateCourse($course_id) {
		$year = date("Y");
		$oldcourse = $this->getCourseByID($course_id);
		if (sizeof($oldcourse) > 0) {
			$oldcourse = $oldcourse[0];
			$newcourseid = $this->createCourse($oldcourse->name, $oldcourse->description, $oldcourse->semester, $year);
			$oldlectures = $this->getLectures($oldcourse->course_id);
			foreach ($oldlectures as $oldlecture) {
				$date = $year.substr($oldlecture->date, 4);
				$lectureid = $this->createLecture($newcourseid, $oldlecture->name, $oldlecture->description, $oldlecture->date);
				$oldmaterials = $this->getMats($oldlecture->lecture_id);
				foreach ($oldmaterials as $oldmaterial) {
					$this->createMaterial($lectureid, $oldmaterial->name, $oldmaterial->url_id, $oldmaterial->type);
				}
			}
			return $newcourseid;
			
		}
		return NULL;
	}
	
	function importMaterialTo($lecture_id, $material_id){
		global $wpdb;
		$m = $wpdb->get_results("SELECT * FROM ".$this->material_table." WHERE material_id=".$material_id);
		$r = $this->createMaterial($lecture_id, $m[0]->name, $m[0]->url_id, $m[0]->type);
		return $r;
	}
		
	function deleteCourse($course_id){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("DELETE FROM " . $this -> course_table . " WHERE course_id = %d;",$course_id));
	}
	
	function deleteMaterial($material_id){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("DELETE FROM " . $this -> material_table . " WHERE material_id = %d;",$material_id));
	}
	
	function deleteLecture($lecture_id){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("DELETE FROM " . $this -> lecture_table . " WHERE lecture_id = %d;",$lecture_id));
	}
	
	function getCourses($year, $semester){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("SELECT * FROM " . $this -> course_table . " WHERE year = %d AND semester = %s;",$year,$semester));
	}
	
	function getAllCourses(){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("SELECT * FROM " . $this -> course_table . ";"));
	}
	
	function getCourseByID($courseid){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("SELECT * FROM " . $this -> course_table . " WHERE course_id = %d;",$courseid));
	}
	
	function getLecture($lecture_id){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("SELECT * FROM " . $this -> lecture_table . " WHERE lecture_id= %d;",$lecture_id));
	}
		
	function getLectures($courseid){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("SELECT * FROM " . $this -> lecture_table . " WHERE course_id= %d ORDER BY date ASC;",$courseid));
	}
	
	function getLecturesAndMaterials($courseid){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("SELECT DISTINCT l.lecture_id, l.name, l.description, l.date,
		m.name, p.guid, m.type FROM " . $this -> lecture_table ." l 
		JOIN ".$this->material_table." m ON l.lecture_id=m.lecture_id 
		JOIN ".$this->posts_table." p ON m.url_id=p.ID WHERE l.course_id= %d;",$courseid));
		
	}
	
	function getMats($lectureid){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("SELECT * FROM " . $this -> material_table . " WHERE lecture_id= %d;",$lectureid));
	}
	
	function getMaterials($lectureid){
		global $wpdb;
		return $wpdb -> get_results($wpdb->prepare("SELECT m.material_id, m.name, m.type, p.guid FROM " . $this -> material_table . " m 
		JOIN ".$this->posts_table." p ON m.url_id=p.ID
		WHERE m.lecture_id= %d ORDER BY m.type DESC;",$lectureid));
	}
	
	function getAllMaterial(){
		global $wpdb;
		return $wpdb->get_results("SELECT c.course_id, 
		l.lecture_id, m.material_id, c.name AS course_name, c.semester, c.year,
		l.name AS lecture_name, l.date, m.name AS material_name, m.type, wp.guid FROM wp_woo_course c 
		JOIN wp_woo_lecture l ON c.course_id=l.course_id JOIN 
		wp_woo_material m ON m.lecture_id=l.lecture_id JOIN wp_posts wp ON m.url_id=wp.ID");
	}
}
?>